package com.api.service;

import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.api.db.DBOperation;
import com.api.db.DBUtils;
import com.api.domain.ExcelData;
import com.api.domain.StatisticsCaseNum;
import com.api.domain.StatisticsData;
import com.api.interfaces.LoginInterface;
import com.api.utils.EditExcelUtils;
import com.api.utils.HelperUtils;
import com.api.utils.JsonHelp;
import com.api.utils.ReadExcelUtils;
import com.api.utils.VerifyMethod;

import jxl.read.biff.BiffException;

/**
 * excel表格操作
 * 
 * @author Administrator
 *
 */
public class OperationExcel {

	String result_actual = "";// 请求后的真实值(判断的值)
	VerifyMethod vm = new VerifyMethod();// 断言方法
	int colorNum = 2;// 默认颜色
	EditExcelUtils eu = new EditExcelUtils();

	List<ExcelData> data;
	String filePath = "";// 文件路径
	ReadExcelUtils reu = new ReadExcelUtils();

	int execyestest;
	int execnotest;
	int exectrue = 0;
	int execfalse = 0;

	StatisticsCaseNum statisticsCaseNum = new StatisticsCaseNum();
	StatisticsCaseNum sc = new StatisticsCaseNum();

	String statistics = "";
	String req_host = "";
	Long startTime1;
	Long endTime1;

	private String caseContent = "";

	private int caseyesNum = 0;
	private int casenoNum = 0;
	private int casegetNum_true = 0;
	private int casegetNum_false = 0;
	private int casepostNum_true = 0;
	private int casepostNum_false = 0;

	String reqType = "";// 请求方式
	String isSuccess = "";// 是否成功

	public int getCaseyesNum() {
		return caseyesNum;
	}

	public void setCaseyesNum(int caseyesNum) {
		this.caseyesNum = caseyesNum;
	}

	public int getCasenoNum() {
		return casenoNum;
	}

	public void setCasenoNum(int casenoNum) {
		this.casenoNum = casenoNum;
	}

	public int getCasegetNum_true() {
		return casegetNum_true;
	}

	public void setCasegetNum_true(int casegetNum_true) {
		this.casegetNum_true = casegetNum_true;
	}

	public int getCasegetNum_false() {
		return casegetNum_false;
	}

	public void setCasegetNum_false(int casegetNum_false) {
		this.casegetNum_false = casegetNum_false;
	}

	public int getCasepostNum_true() {
		return casepostNum_true;
	}

	public void setCasepostNum_true(int casepostNum_true) {
		this.casepostNum_true = casepostNum_true;
	}

	public int getCasepostNum_false() {
		return casepostNum_false;
	}

	public void setCasepostNum_false(int casepostNum_false) {
		this.casepostNum_false = casepostNum_false;
	}

	public String getCaseContent() {
		return caseContent;
	}

	public void setCaseContent(String caseContent) {
		this.caseContent = caseContent;
	}

	Double alltime = 0.000;

	StatisticsData sd = new StatisticsData();
	String req_interface = "";

	String assertResult = "";
	Double maxtime;

	/**
	 * 读取excel表数据
	 * 
	 * @param fileName
	 *            文件名
	 */
	public StatisticsCaseNum readExcel(Object fileName) {

		filePath = (String) fileName;
		data = reu.readExcel(filePath);
		statisticsCaseNum.setExecyestest(0);
		statisticsCaseNum.setExecnotest(0);
		execyestest = 0;
		execnotest = 0;

		if (data != null) {
			maxtime = 0.000;
			for (int i = 0; i <= data.size() - 1; i++) {
				String[] sp = data.get(i).getReqUrl().split("[?]");
				statistics = data.get(i).getClassification();
				String[] sphost = sp[0].split("m");
				req_host = sphost[0] + "m";
				req_interface = sphost[1].substring(1, sphost[1].length() - 1);

				if (data.get(i).getIsExec().equals("YES")) {
					execyestest++;
					setCaseyesNum(1);
					reqType = data.get(i).getType();

					if (data.get(i).getType().equals("POST")) {
						execPostData(i);
					} else if (data.get(i).getType().equals("GET")) {
						execGetData(i);
					} else {
						System.out.println("Req_type值只能是POST或GET！");
					}
				} else if (data.get(i).getIsExec().equals("NO")) {
					execnotest++;
					setCasenoNum(1);
					reqType = "";
					sd.setResponseTime(0.00);
				} else {
					System.out.println("Test_is_exec值只能是YES或NO！");
				}

				caseContent += getCaseStatisticsData(i);
				setCaseContent(caseContent);

				setCaseyesNum(0);
				setCasenoNum(0);

				statisticsCaseNum.setExecyestest(execyestest);
				statisticsCaseNum.setExecnotest(execnotest);
			}
			sd.setAverageTime(Double.parseDouble(HelperUtils.formatDouble(alltime / execyestest)));
		}
		exectrue = 0;
		execfalse = 0;
		alltime = 0.0;

		sc.setExecyestest(execyestest);
		sc.setExecnotest(execnotest);
		return sc;
	}

	/**
	 * 获取分割后的数据
	 * 
	 * @param userName
	 */
	public List<String> getSplitList(String userName, String flag) {

		List<String> list = new ArrayList<String>();
		String[] uName = null;

		if (userName != null && userName.length() > 0 && flag.length() > 0) {
			if (userName.contains(flag)) {
				uName = userName.split(flag);
				for (int i = 0; i < uName.length; i++) {
					list.add(i, uName[i]);
				}
			} else {
				list.add(0, userName);
			}
		}
		return list;
	}

	/**
	 * 获取用户的cookie信息
	 * 
	 * @param userName
	 * @return
	 */
	public List<String> getUserCookies(String userName) {
		List<String> cookies = new ArrayList<String>();
		if (userName.contains(">")) {
			List<String> uName = getSplitList(userName, ">");
			for (int j = 0; j < uName.size(); j++) {
				// 调试使用
				cookies.add(LoginInterface.getCookie(uName.get(j)));
			}
		} else {
			List<String> uName = getSplitList(userName, "");
			for (int j = 0; j < uName.size(); j++) {
				cookies.add(LoginInterface.getCookie(uName.get(j)));
			}
		}

		return cookies;
	}

	/**
	 * 以>分割字符串
	 * 
	 * @param st
	 * @return
	 */
	public List<String> splitList(String st) {
		List<String> list = new ArrayList<String>();
		if (st.contains(">")) {
			list = getSplitList(st, ">");
		} else {
			list = getSplitList(st, ";");
		}
		return list;
	}

	/**
	 * get请求
	 * 
	 * @param i
	 */
	private void execGetData(int i) {
		// 获取用户的cookies信息
		List<String> userCookies = getUserCookies(data.get(i).getUserName());
		execteConditions(i, userCookies, data.get(i).getCoditionsType());

		data.get(i).setExecTime(HelperUtils.getNowData(new Date()));

		startTime1 = System.currentTimeMillis();

		// 执行测试用例
		result_actual = execteGetCase(userCookies, data.get(i).getReqUrl());

		endTime1 = System.currentTimeMillis();
		Double rt = (endTime1 - startTime1) / 1000.000;
		sd.setResponseTime(rt);

		// 判断接口最大响应时间
		if (maxtime < rt) {
			maxtime = rt;
			sd.setMaxTime(rt);
		}
		data.get(i).setActualValue(result_actual);

		// 后置条件
		execteExsql(i, userCookies);

		// 获取期望参数
		String expParams = data.get(i).getExpectedParams();

		List<String> expecParams = new ArrayList<String>();
		if (expParams != null && expParams.length() > 0) {
			expecParams = splitList(expParams);
		}

		// 获取期望结果
		execteExpectedMethod(i, userCookies, expecParams, data.get(i).getExpectedUrl());

		// 执行sql
		assertResult = assertMethod(i, data.get(i).getExpectedValue());
		updateExcelData(i, data.get(i).getActualValue(), data.get(i).getConditionsValue(),
				data.get(i).getExpectedValue(), assertResult);
	}

	/**
	 * 执行sql（url请求之后操作）
	 * 
	 * @param i
	 * @param userCookies
	 */
	private void execteExsql(int i, List<String> userCookies) {
		List<String> exsql = getSplitList(data.get(i).getSql(), ";");
		String sqlResult = "";
		for (int j = 0; j < exsql.size(); j++) {

			if (exsql.get(j).contains("SELECT")) {
				sqlResult = execteSqlMethod(exsql.get(j));
			} else if (exsql.get(j).contains("UPDATE")) {
				execteSqlMethod(exsql.get(j) + "'" + sqlResult + "'");
			} else if (exsql.get(j).contains("https")) {
				String dd = ReqPort.reqTypeGet(exsql.get(j) + sqlResult, userCookies.get(0));
			}
		}
	}

	/**
	 * 执行post的请求
	 * 
	 * @param i
	 */
	private void execPostData(int i) {
		// 获取用户的cookies信息
		List<String> userCookies = getUserCookies(data.get(i).getUserName());

		execteConditions(i, userCookies, data.get(i).getCoditionsType());

		data.get(i).setExecTime(HelperUtils.getNowData(new Date()));

		startTime1 = System.currentTimeMillis();
		// 执行测试用例
		result_actual = exectePostCase(userCookies, data.get(i).getReqUrl());

		endTime1 = System.currentTimeMillis();
		Double rt = (endTime1 - startTime1) / 1000.000;
		sd.setResponseTime(rt);
		// 判断接口最大响应时间
		if (maxtime < rt) {
			maxtime = rt;
			sd.setMaxTime(rt);
		}

		data.get(i).setActualValue(result_actual);
		// 后置条件
		execteExsql(i, userCookies);

		// 获取期望参数
		String expParams = data.get(i).getExpectedParams();

		List<String> expecParams = new ArrayList<String>();
		if (expParams != null && expParams.length() > 0) {
			expecParams = splitList(expParams);
		}
		// 获取期望结果
		execteExpectedMethod(i, userCookies, expecParams, data.get(i).getExpectedUrl());

		assertResult = assertMethod(i, data.get(i).getExpectedValue());
		updateExcelData(i, data.get(i).getActualValue(), data.get(i).getConditionsValue(),
				data.get(i).getExpectedValue(), assertResult);
	}

	/**
	 * 执行前置条件
	 * 
	 * @param i
	 * @param userCookies
	 * @param condType
	 */
	private void execteConditions(int i, List<String> userCookies, String condType) {
		if (condType != null && condType.length() > 0) {
			// 获取前置条件请求类型
			List<String> codTypeList = splitList(condType);
			// 获取请求的参数
			String condKey = data.get(i).getConditionsParams();
			List<String> codparams = new ArrayList<String>();
			// 判断是否有前置条件
			if (condKey != null && condKey.length() > 0) {
				codparams = splitList(condKey);
			}
			conditionsMethod(i, userCookies, codTypeList, codparams, data.get(i).getConditions());
		}
	}

	/**
	 * 获取前置条件的值
	 * 
	 * @param i
	 * @param userCookies
	 * @param codTypeList
	 * @param codparams
	 * @param condtions
	 */
	private void conditionsMethod(int i, List<String> userCookies, List<String> codTypeList, List<String> codparams,
			String condtions) {

		if (condtions != null && condtions.length() > 0) {
			String conditionsValue = "";
			List<String> codt;

			List<String> conditionsUrlList = splitList(condtions);

			for (int j = 0; j < codTypeList.size(); j++) {
				// 获取请求类型，以>分隔符区分
				codt = splitList(codTypeList.get(j));

				// 前置条件判断
				if (condtions.length() > 0) {
					// 获取验证的参数
					List<String> codpar = getSplitList(codparams.get(j), ";");
					// 获取用户的cookie
					String cookie = userCookies.get(j);
					// 获取每个请求url
					List<String> conUrl = getSplitList(conditionsUrlList.get(j), ";");

					for (int l = 0; l < conUrl.size(); l++) {// 便利单个请求url
						// 获取对应的请求类型
						String type = codt.get(l);
						// 获取对应前置条件要验证的参数
						String codpara = codpar.get(l);
						String conresult = "";
						// 判断使用什么请求类型

						if (type.equalsIgnoreCase("get")) {
							conresult = ReqPort.reqTypeGet(conUrl.get(l), cookie);
						} else if (type.equalsIgnoreCase("post")) {
							conresult = ReqPort.reqTypePost(conUrl.get(l), cookie);
							// 发送post请求
						} else if (type.equalsIgnoreCase("sql")) {// 操作数据库
							// 发送sql请求
							execteSqlMethod(conUrl.get(l));
						} else {
							System.out.println(conUrl.get(l) + ":发送的请求类型不正确");
						}

						// 如果前置条件的参数不等于空
						if (codpara != null && codpara.length() > 0) {
							if (codpara.contains("sql")) {
							} else {
								// 截取前置条件参数
								String[] codkey = codpara.split(",");
								// //获取前置参数条件值
								for (int m = 0; m < codkey.length; m++) {
									conditionsValue += JsonHelp.getJsonValue(conresult, codkey[m]) + ",";
								}
							}
						}
						if (conditionsValue.length() > 0) {
							conditionsValue = conditionsValue.substring(0, conditionsValue.length() - 1) + ";";
						}

					}
					conditionsValue = conditionsValue.substring(0, conditionsValue.length() - 1) + ">";
				}

			}

			data.get(i).setConditionsValue(conditionsValue.substring(0, conditionsValue.length() - 1));
		}

	}

	/**
	 * 执行期望值url，获取真实值
	 * 
	 * @param i
	 * @param userCookies
	 * @param expecParams
	 * @param expect
	 */
	private void execteExpectedMethod(int i, List<String> userCookies, List<String> expecParams, String expect) {
		String actuValue = "";
		List<String> expectUrlList = splitList(data.get(i).getExpectedUrl());
		if (expect.length() > 0) {
			for (int j = 0; j < expectUrlList.size(); j++) {

				List<String> expecpar = getSplitList(expecParams.get(j), ";");

				String cookie = userCookies.get(j);
				List<String> exurl = getSplitList(expectUrlList.get(j), ";");

				String result = "";
				for (int k = 0; k < exurl.size(); k++) {
					String expara = expecpar.get(k);

					if (expara.contains("sql")) {
						String ddString = execteSqlMethod(exurl.get(k));
						actuValue += ddString;
					} else {

						try {
							Thread.sleep(3000);
						} catch (InterruptedException e) {
							e.printStackTrace();
						}
						result = ReqPort.reqTypeGet(exurl.get(k), cookie);

						// 期望值
						if (expara != null && expara.length() > 0) {
							// 截取前置条件参数
							String[] codkey = expara.split(",");
							// //获取前置参数条件值
							for (int m = 0; m < codkey.length; m++) {
								actuValue += JsonHelp.getJsonValue(result, codkey[m]) + ",";
							}
						}

					}

					actuValue = actuValue.substring(0, actuValue.length() - 1) + ";";
				}
				actuValue = actuValue.substring(0, actuValue.length() - 1) + ">";
			}

			actuValue = actuValue.substring(0, actuValue.length() - 1);
			data.get(i).setActualValue(actuValue);
		}

	}

	/**
	 * 期望结果判断
	 * 
	 * @param i
	 * @param expected
	 * @return
	 */
	private String assertMethod(int i, String expected) {
		String result = "";
		if (expected != null && expected.length() > 0) {
			List<String> ex = splitList(expected);
			if (ex.size() > 0) {

				for (int j = 0; j < ex.size(); j++) {
					if (ex.contains(";")) {
						List<String> explist = getSplitList(ex.get(j), ";");
						for (int j2 = 0; j2 < explist.size(); j2++) {
							String[] exc = explist.get(j2).split(",");
							for (int k = 0; k < exc.length; k++) {
								result += HelperUtils.assertResult(exc[k], data.get(i).getActualValue());
							}
						}

					} else {
						String[] exc = ex.get(j).split(",");
						for (int k = 0; k < exc.length; k++) {
							result += HelperUtils.assertResult(exc[k], data.get(i).getActualValue());
						}
					}

				}

			}
		}

		if (result.contains("FALSE")) {
			result = "FALSE";
			execfalse++;
			scexecfalse++;
			statisticsCaseNum.setExecfalse(execfalse);
			sc.setExecfalse(scexecfalse);
			setCasegetNum_true(1);
		} else {
			result = "TRUE";
			exectrue++;
			scexectrue++;
			statisticsCaseNum.setExectrue(exectrue);
			sc.setExectrue(scexectrue);
			setCasegetNum_false(1);
		}
		return result;
	}

	int scexecfalse;
	int scexectrue;

	/**
	 * 执行请求的sql
	 * 
	 * @param reqSql
	 */
	private String execteSqlMethod(String reqSql) {

		String result = "";
		if (reqSql.length() > 0) {
			DBOperation db = new DBOperation();
			if (reqSql.contains(";")) {
				List<String> sqList = getSplitList(reqSql, ";");
				for (int j = 0; j < sqList.size(); j++) {
					if (sqList.get(j).contains("UPDATE")) {
						DBUtils.updateSQL(sqList.get(j));
					} else if (sqList.get(j).contains("SELECT")) {
						result = db.getSQLResult(sqList.get(j));
					}
				}
			} else {
				if (reqSql.contains("UPDATE")) {
					DBUtils.updateSQL(reqSql);
				} else if (reqSql.contains("SELECT")) {
					result = db.getSQLResult(reqSql);
				}
			}

		}
		return result;
	}

	/**
	 * 执行测试用例POST方法
	 * 
	 * @param userCookies
	 * @param reqUrl
	 * @return，返回真实结果
	 */
	private String exectePostCase(List<String> userCookies, String reqUrl) {
		String result = "";
		List<String> urList = new ArrayList<String>();
		if (reqUrl != null && reqUrl.length() > 0) {// 多个请求url
			urList = splitList(reqUrl);
			for (int l = 0; l < urList.size(); l++) {
				// userCookies.get(0)默认第一个用户为请求的cookie
				result += ReqPort.reqTypePost(urList.get(l), userCookies.get(0));
			}
		}
		return result;
	}

	/**
	 * 执行get的方法
	 * 
	 * @param userCookies
	 * @param reqUrl
	 * @return
	 */
	private String execteGetCase(List<String> userCookies, String reqUrl) {
		String result = "";
		List<String> urList = new ArrayList<String>();
		if (reqUrl != null && reqUrl.length() > 0) {// 多个请求url
			urList = splitList(reqUrl);
			for (int l = 0; l < urList.size(); l++) {
				// userCookies.get(0)默认第一个用户为请求的cookie
				result += ReqPort.reqTypeGet(urList.get(l), userCookies.get(0));
			}
		}
		return result;
	}

	/**
	 * 更新excel表格数据
	 * 
	 * @param i
	 * @param actualValue
	 * @param condValue
	 * @param expectValue
	 * @param assertResult
	 */
	private void updateExcelData(int i, String actualValue, String condValue, String expectValue, String assertResult) {
		if (assertResult.equals("TRUE")) {
			colorNum = 3;
		} else if (assertResult.equals("FALSE")) {
			colorNum = 4;
		}
		try {
			eu.updateExcel(filePath, 8, i + 1, condValue, 5);
			eu.updateExcel(filePath, 14, i + 1, expectValue, 2);
			eu.updateExcel(filePath, 15, i + 1, actualValue, colorNum);
			eu.updateExcel(filePath, 16, i + 1, assertResult, colorNum);
			eu.updateExcel(filePath, 17, i + 1, data.get(i).getExecTime(), 2);

		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

	}

	/**
	 * 获取不同接口统计信息
	 * 
	 * @return
	 */
	public String getStatisticsData() {

		String successRate = HelperUtils.num_percent(statisticsCaseNum.getExectrue(),
				statisticsCaseNum.getExecyestest());
		String failRate = HelperUtils.num_percent(statisticsCaseNum.getExecfalse(), statisticsCaseNum.getExecyestest());
		String data = "";
		data = "<tr>"

				+ "<td align='center' style='background:#99CCFF'>" + statistics + "</td>"
				+ "<td style='background:#99CCFF'>" + req_host + "</td>"
				+ "<td align='center' style='background:#99CCFF'>"
				+ (statisticsCaseNum.getExecyestest() + statisticsCaseNum.getExecnotest()) + "</td>"
				+ "<td align='center' style='background:#99CCFF'>" + statisticsCaseNum.getExecyestest() + "</td>"
				+ "<td align='center' style='background:#99CCFF'>" + statisticsCaseNum.getExecnotest() + "</td>"
				+ "<td align='center' style='background:#99CCFF'>" + "--" + "</td>"
				+ "<td align='center' style='background:#99CCFF'>" + "--" + "</td>"
				+ "<td align='center' style='background:#99CCFF'>" + "--" + "</td>"
				+ "<td align='center' style='background:#99CCFF'><font color='red'>" + sd.getAverageTime()
				+ "s</font></td>" + "<td align='center' style='background:#99CCFF'><font color='red'>" + sd.getMaxTime()
				+ "s</font></td>"
				+ "<td align='center' style='background:#99CCFF'><font color='red'>" + successRate + "</font></td>"
				+ "<td align='center' style='background:#99CCFF'><font color='red'>" + failRate + "</font></td>"

				+ "</tr>" + getCaseContent();
		setCaseContent("");
		statisticsCaseNum.setExectrue(0);
		statisticsCaseNum.setExecfalse(0);
		return data;
	}

	/**
	 * 获取用例统计信息
	 * 
	 * @return
	 */
	public String getCaseStatisticsData(int i) {
		String data = "";

		if (assertResult.equals("TRUE")) {
			isSuccess = "成功";
		} else if (assertResult.equals("FALSE")) {
			isSuccess = "失败";
		} else if (assertResult == null || assertResult.equals("")) {
			isSuccess = "未执行";
		}
		alltime += sd.getResponseTime();
		data = "<tr>" + "<td></td>" + "<td>" + req_interface + "</td>" + "<td align='center'>1</td>"
				+ "<td align='center'>" + getCaseyesNum() + "</td>" + "<td align='center'>" + getCasenoNum() + "</td>"
				+ "<td align='center'>" + reqType + "</td>" + "<td align='center'>" + isSuccess + "</td>"
				+ "<td align='center'><font color='green'>" + sd.getResponseTime() + "s</font></td>"
				+ "<td align='center'></td>" + "<td align='center'></td>"
				+ "<td align='center'></td>" + "<td align='center'></td>"
				+ "</tr>";

		assertResult = "";
		return data;
	}

}
